import pandas as pd
import decimal

# 数学上的四舍五入(避免Python原生round 的银行家舍入)
decimal.getcontext().rounding = "ROUND_HALF_UP"


class RationalUpCalServer:
    """
    合理性和增长性数据（采集）业务类
    """

    def __init__(self, pre_data_path, now_data_path, area_sample):
        """
        初始化
        :param pre_data_path: 上季度分户路径
        :param now_data_path: 当前季度分户数据
        :param area_sample: 小区样本路径
        :param curent_quarter: 当前季度 (1 当前季度  0 上一季度)
        :param urban_rural: 城镇还是农村(城镇U UR 农村 R)
        """
        pre_data = pd.read_csv(pre_data_path, encoding='gbk')
        now_data = pd.read_csv(now_data_path, encoding='gbk')
        area_sample = pd.read_csv(area_sample, encoding='gbk',skiprows=[1])

        pre_data["小区ID"] = pre_data["sID"].astype(str).apply(lambda sid: sid[0:sid.find("H")])
        now_data["小区ID"] = now_data["sID"].astype(str).apply(lambda sid: sid[0:sid.find("H")])
        area_sample["VID调查小区ID"] = area_sample["VID调查小区ID"].astype(str)

        self.pre_data_df = pd.merge(pre_data, area_sample[["VID调查小区ID", "LEVEL2城乡类别"]].rename(
            columns={"LEVEL2城乡类别": "城乡类别"}), left_on="小区ID",
                                    right_on="VID调查小区ID", how="left").copy()
        self.now_data_df = pd.merge(now_data, area_sample[["VID调查小区ID", "LEVEL2城乡类别"]].rename(
            columns={"LEVEL2城乡类别": "城乡类别"}), left_on="小区ID",
                                    right_on="VID调查小区ID", how="left").copy()
        # 获取连续户
        con_df_sID = pd.merge(self.pre_data_df, self.now_data_df, on='sID', how='inner')["sID"]
        self.pre_con_df = self.pre_data_df[self.pre_data_df["sID"].isin(con_df_sID)].copy()
        self.now_con_df = self.now_data_df[self.now_data_df["sID"].isin(con_df_sID)].copy()
        list_data = [self.pre_data_df, self.pre_con_df, self.now_data_df, self.now_con_df]
        for data in list_data:
            data = self.__init_data(data)



    def __init_data(self, data):
        """
        初始化表
        :param data:
        :return:
        """
        # 加权人数
        data["加权N205"] = data["N0205"].astype(float) * data["hhWeight"].astype(float) * data["vWeight"].astype(float)
        # 加权N1100 收入
        data["加权N1100"] = data["N1100"].astype(float) * data["hhWeight"].astype(float) * data["vWeight"].astype(float)
        # 加权N2001 消费
        data["加权N2001"] = data["N2001"].astype(float) * data["hhWeight"].astype(float) * data["vWeight"].astype(float)
        # 加权N1100 人均
        data["加权N1100人均"] = data["加权N1100"] / data["加权N205"]
        # # 不加权N1100 人均
        # data["不加权N1100人均"] = data["N1100"] / data["N0205"]
        # 加权N2001 人均
        data["加权N2001人均"] = data["加权N2001"] / data["加权N205"]
        # # 不加权N2001 人均
        # data["不加权N2001人均"] = data["N2001"] / data["N0205"]

        return data

    def 加权人均(self):
        print(self.pre_data_df)
        pass

    def 不加权人均(self, df):
        res = []
        # 人均收入
        res.append(df["N1100"].sum() / df["N0205"].sum())
        # 人均消费支出
        res.append(df["N2001"].sum() / df["N0205"].sum())
        return res

    # 连续户
    # def 加权剔除最高和最低2(self, df, rationNum=0.02):
    #     # res = []
    #     # # 获取剔除户的数量
    #     # ignoreNums = int(decimal.Decimal(str(df.shape[0] * rationNum)).quantize(decimal.Decimal("0")))
    #     # # N1100人均去除2%最高最低，求和
    #     # resHouseDf = df.sort_values(by=["加权N1100人均"])[ignoreNums:df.shape[0] - ignoreNums]
    #     # res.append(resHouseDf["加权N1100"].sum() / resHouseDf["加权N205"].sum())
    #     # resHouseDf.to_excel("./b.xlsx",index=False)
    #     # # N2001人均去除2%最高最低，求和
    #     # resHouseDf = df.sort_values(by=["加权N2001人均"])[ignoreNums:df.shape[0] - ignoreNums]
    #     # res.append(resHouseDf["加权N2001"].sum() / resHouseDf["加权N205"].sum())
    #     res = []
    #     conHousesDf = pd.merge(self.now_con_df[self.now_con_df["sID"].isin(df["sID"])],
    #                            self.pre_con_df[self.pre_con_df["sID"].isin(df["sID"])],
    #                            on="sID",
    #                            suffixes=["", "_"], how="inner")
    #     # 计算增幅 2024  _ 2023
    #     conHousesDf["加权N1100人均收入增幅"] = (conHousesDf["加权N1100人均"] - conHousesDf["加权N1100人均_"]) / \
    #                                            conHousesDf[
    #                                                "加权N1100人均_"] * 100
    #     conHousesDf["加权N2001人均消费增幅"] = (conHousesDf["加权N2001人均"] - conHousesDf["加权N2001人均_"]) / \
    #                                            conHousesDf[
    #                                                "加权N2001人均_"] * 100
    #
    #     # 获取剔除户连续户的数量
    #     ignoreNums = int(decimal.Decimal(str(df.shape[0] * rationNum)).quantize(decimal.Decimal("0")))
    #
    #     # 加权N1100人均收入增幅去除2%最高最低，求和
    #     conHouse_2_id = list(conHousesDf.sort_values(by=["加权N1100人均收入增幅"])[ignoreNums:conHousesDf.shape[0] - ignoreNums]["sID"])
    #
    #     resHouseDf = df[df["sID"].isin(conHouse_2_id)]
    #     res.append(resHouseDf["加权N1100"].sum() / resHouseDf["加权N205"].sum())
    #     # resHouseDf.to_excel("./b.xlsx",index=False)
    #     # N2001人均去除2%最高最低，求和
    #     conHouse_2_id = list(
    #         conHousesDf.sort_values(by=["加权N2001人均消费增幅"])[ignoreNums:conHousesDf.shape[0] - ignoreNums]["sID"])
    #     resHouseDf = df[df["sID"].isin(conHouse_2_id)]
    #     res.append(resHouseDf["加权N2001"].sum() / resHouseDf["加权N205"].sum())
    #     return res

    # 连续户 2%
    def 加权剔除最高和最低2(self, df, rationNum=0.02):
        # res = []
        # # 获取剔除户的数量
        # ignoreNums = int(decimal.Decimal(str(df.shape[0] * rationNum)).quantize(decimal.Decimal("0")))
        # # N1100人均去除2%最高最低，求和
        # resHouseDf = df.sort_values(by=["加权N1100人均"])[ignoreNums:df.shape[0] - ignoreNums]
        # res.append(resHouseDf["加权N1100"].sum() / resHouseDf["加权N205"].sum())
        # resHouseDf.to_excel("./b.xlsx",index=False)
        # # N2001人均去除2%最高最低，求和
        # resHouseDf = df.sort_values(by=["加权N2001人均"])[ignoreNums:df.shape[0] - ignoreNums]
        # res.append(resHouseDf["加权N2001"].sum() / resHouseDf["加权N205"].sum())
        res = []
        conHousesDf = pd.merge(self.now_con_df[self.now_con_df["sID"].isin(df["sID"])],
                               self.pre_con_df[self.pre_con_df["sID"].isin(df["sID"])],
                               on="sID",
                               suffixes=["", "_"], how="inner")
        # 计算增幅 2024  _ 2023
        conHousesDf["加权N1100人均收入增幅"] = (conHousesDf["加权N1100人均"] - conHousesDf["加权N1100人均_"]) / \
                                               conHousesDf[
                                                   "加权N1100人均_"] * 100
        conHousesDf["加权N2001人均消费增幅"] = (conHousesDf["加权N2001人均"] - conHousesDf["加权N2001人均_"]) / \
                                               conHousesDf[
                                                   "加权N2001人均_"] * 100

        # 获取剔除户连续户的数量
        ignoreNums = int(decimal.Decimal(str(conHousesDf.shape[0] * rationNum)).quantize(decimal.Decimal("0")))

        # 加权N1100人均收入增幅去除2%最高最低，求和
        conHouse_2_id = list(conHousesDf.sort_values(by=["加权N1100人均收入增幅"])[ignoreNums:conHousesDf.shape[0] - ignoreNums]["sID"])

        # 获取过滤的sID
        conHouse_2_id_ignore = conHousesDf[~conHousesDf["sID"].isin(conHouse_2_id)]["sID"]
        res_ids = df[~df["sID"].isin(conHouse_2_id_ignore)]["sID"]
        resHouseDf = df[df["sID"].isin(conHouse_2_id)]
        # 共同户
        self.connnn = conHousesDf
        # 剔除后共同户
        self.resHouseDf_out = resHouseDf.copy()
        # 剔除户
        self.out_hu = conHousesDf[~conHousesDf["sID"].isin(conHouse_2_id)]
        res.append(resHouseDf["加权N1100"].sum() / resHouseDf["加权N205"].sum())
        # resHouseDf.to_excel("./b.xlsx",index=False)
        # N2001人均去除2%最高最低，求和
        conHouse_2_id = list(
            conHousesDf.sort_values(by=["加权N2001人均消费增幅"])[ignoreNums:conHousesDf.shape[0] - ignoreNums]["sID"])
        # 获取过滤的sID
        conHouse_2_id_ignore = conHousesDf[~conHousesDf["sID"].isin(conHouse_2_id)]["sID"]
        res_ids = df[~df["sID"].isin(conHouse_2_id_ignore)]["sID"]
        resHouseDf = df[df["sID"].isin(conHouse_2_id)]
        # resHouseDf = df[df["sID"].isin(conHouse_2_id)]
        res.append(resHouseDf["加权N2001"].sum() / resHouseDf["加权N205"].sum())
        return res

    def 加权中位户绝对值(self, df):
        res = []
        res.append(df["加权N1100人均"].median())
        res.append(df["加权N2001人均"].median())
        return res

    def 加权连续户绝对值(self, df):
        res = []
        # 人均收入
        res.append(df["加权N1100"].sum() / df["加权N205"].sum())
        # 人均消费支出
        res.append(df["加权N2001"].sum() / df["加权N205"].sum())
        # # 人均收入
        # res.append(df["加权N1100人均"].sum() / df.shape[0])
        # # 人均消费支出
        # res.append(df["加权N2001人均"].sum() / df.shape[0])
        return res

    def 连续户_收入增长_消费增长(self, df):
        res = []

        conHousesDf = pd.merge(self.now_con_df[self.now_con_df["sID"].isin(df["sID"])],
                               self.pre_con_df[self.pre_con_df["sID"].isin(df["sID"])],
                               on="sID",
                               suffixes=["", "_"], how="inner")
        # 计算增幅 2024  _ 2023
        conHousesDf["加权N1100人均收入增幅"] = (conHousesDf["加权N1100人均"] - conHousesDf["加权N1100人均_"]) / \
                                               conHousesDf[
                                                   "加权N1100人均_"] * 100
        conHousesDf["加权N2001人均消费增幅"] = (conHousesDf["加权N2001人均"] - conHousesDf["加权N2001人均_"]) / \
                                               conHousesDf[
                                                   "加权N2001人均_"] * 100
        # 连续户数
        res.append(conHousesDf.shape[0])
        # 连续户收入增长户数
        res.append(conHousesDf[conHousesDf["加权N1100人均收入增幅"] > 0].shape[0])
        # 连续户消费增长户数
        res.append(conHousesDf[conHousesDf["加权N2001人均消费增幅"] > 0].shape[0])

        return res

    def layout_data(self, df,con_df,current_quarter = 0):
        res = []
        res.extend(self.不加权人均(df))
        res.extend(self.加权剔除最高和最低2(df))
        res.extend(self.加权中位户绝对值(df))
        res.extend(self.加权连续户绝对值(con_df))
        if current_quarter == 1:
            res.extend(self.连续户_收入增长_消费增长(con_df))
        return res


if __name__ == '__main__':
    pre_data_path = r"E:\泸州调查队居民收支科\开发\PythonWorkSpace\data\马梦江1 - 副本\2023年前三季度分户 - 副本.csv"
    now_data_path = r"E:\泸州调查队居民收支科\开发\PythonWorkSpace\data\马梦江1 - 副本\2024年前三季度分户 - 副本.csv"
    area_sample = r"E:\泸州调查队居民收支科\开发\PythonWorkSpace\data\马梦江1 - 副本\小区样本.csv"

    # 所有共同户
    # con = pd.merge(pd.read_csv(now_data_path, encoding='gbk'),
    #                pd.read_csv(pre_data_path, encoding='gbk'),
    #                on="sID",
    #                suffixes=["", "_"], how="inner")
    # con.to_excel("./所有共同户.xlsx", index=False)

    a = RationalUpCalServer(pre_data_path, now_data_path, area_sample)
    # temp = a.pre_con_df[a.pre_con_df["城乡类别"].isin(["R"])].copy()
    temp = a.now_data_df.copy()
    temp = a.now_data_df[a.now_data_df["城乡类别"].isin(["U","UR"])].copy()
    temp = temp[temp["coun"] == 510502]
    # temp.to_excel("./a.xlsx",index=False)
    # print(temp.keys())
    # print(a.连续户_收入增长_消费增长(temp))
    # temp.to_excel("./a.xlsx", index=False)
    print(a.加权剔除最高和最低2(temp))
    # print(temp)
    # a.加权人均()
    # print(a.不加权人均(temp))
    # l = [1,2,3]
    # print(l[1])
